/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2021/10/26 9:59:39                           */
/*==============================================================*/
create database OADatabase
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('staffInfo') and o.name = 'FK_STAFFINF_REFERENCE_DEPARTME')
alter table staffInfo
   drop constraint FK_STAFFINF_REFERENCE_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"user table"') and o.name = 'FK_USER TAB_REFERENCE_DEPARTME')
alter table "user table"
   drop constraint "FK_USER TAB_REFERENCE_DEPARTME"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"Department information"')
            and   type = 'U')
   drop table "Department information"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('staffInfo')
            and   type = 'U')
   drop table staffInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"user table"')
            and   type = 'U')
   drop table "user table"
go

/*==============================================================*/
/* Table: "Department information"                              */
/*==============================================================*/
create table "Department information" (
   departmentId         int                  not null,
   departmentName       nvarchar(20)        not null,
   "competent department"  nvarchar(20)       not null,
   Phone                 nvarchar(20)      not null,
   "email address"       nvarchar(20)       not null,
   constraint "PK_DEPARTMENT INFORMATION" primary key (departmentId),
   constraint AK_KEY_1_DEPARTME unique ("competent department")
)
go

/*==============================================================*/
/* Table: staffInfo                                             */
/*==============================================================*/
create table staffInfo (
   Id                   int                  not null,
   departmentId         int                  null,
   Name                 nvarchar(20)         null,
   position             nvarchar(20)         null,
   Phone                nvarchar(20)         null,
   Compensation         nvarchar(20)         null,
   constraint PK_STAFFINFO primary key (Id)
)
go

/*==============================================================*/
/* Table: "user table"                                          */
/*==============================================================*/
create table "user table" (
   ID                   int                  not null,
   departmentId         int                  null,
   Name                 nvarchar(20)         null,
   "bank card"          nvarchar(20)         null,
   Phone                nvarchar(20)         null,
   Address              nvarchar(20)         null,
   constraint "PK_USER TABLE" primary key (ID)
)
go

alter table staffInfo
   add constraint FK_STAFFINF_REFERENCE_DEPARTME foreign key (departmentId)
      references "Department information" (departmentId)
go

alter table "user table"
   add constraint "FK_USER TAB_REFERENCE_DEPARTME" foreign key (departmentId)
      references "Department information" (departmentId)
go

